﻿
Public Class AccesoBD
    Dim conexion, conTran As New Object
    Dim cmd, cmdTran As New Object
    Dim transaccion As OleDb.OleDbTransaction
    Dim _resultado As estado_transaccion
    Enum estado_transaccion
        _ok
        _error
    End Enum

    'string conexion jacky 
    Dim string_conexion As String = "Data Source=JACKY-PC\SQLEXPRESS;Initial Catalog=bd_academia;Integrated Security=SSPI;Provider=SQLOLEDB"
    'string conexion nehemias
    ' Dim string_conexion As String = "Provider=SQLOLEDB;Data Source=NEHEMÍAS-NTBK\SQLSERVER2008;Integrated Security=SSPI;Initial Catalog=bd_academia"
    'string conexion gaby
    'Dim string_conexion As String = "Provider=SQLOLEDB;Data Source=GABY-PC\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=bd_academia"
    'metodo para realizar la conexion a la BD
    Private Sub conectar()
        Me.conexion = New OleDb.OleDbConnection
        Me.cmd = New OleDb.OleDbCommand

        Me.conexion.ConnectionString = Me.string_conexion
        Me.conexion.Open()
        Me.cmd.Connection = conexion
        Me.cmd.CommandType = CommandType.Text
    End Sub

    'metodo que devuelve una tabla especificada por parametro
    Public Function devuelveTabla(ByVal nombre_tabla As String) As DataTable
        Dim tabla As New DataTable

        Me.conectar()
        Me.cmd.CommandText = "select * from " & nombre_tabla
        tabla.Load(Me.cmd.ExecuteReader())
        Me.conexion.Close()

        Return tabla
    End Function

    'metodo que ejecuta una consulta SQL pasada por parametro y devuelve una tabla
    Public Function ejecutaSQL(ByVal consulta As String) As DataTable
        Dim tabla As New DataTable

        Me.conectar()
        Me.cmd.CommandText = consulta

        tabla.Load(Me.cmd.ExecuteReader())
        conexion.Close()

        Return tabla
    End Function

    'metodo que carga combos genericamente
    Public Sub load_combo(ByRef combo As ComboBox, ByVal datos As DataTable, ByVal pk As String, ByVal descripcion As String)
        'combo.Items.Clear()
        combo.DataSource = datos
        combo.DisplayMember = descripcion
        combo.ValueMember = pk
        combo.SelectedValue = -1
    End Sub
    'metodo para transacciones. Usar para inseRt, delete o update
    Private Sub execute(ByVal comando_sql As String)
        Me.conectar()
        Me.cmd.CommandText = comando_sql
        Me.cmd.ExecuteNonQuery()
        conexion.Close()
    End Sub

    Public Sub _insertar(ByVal comando_sql As String)
        Me.execute(comando_sql)
    End Sub

    Public Sub _modificar(ByVal comando_sql As String)
        Me.execute(comando_sql)
    End Sub

    Public Sub _eliminar(ByVal comando_sql As String)
        Me.execute(comando_sql)
    End Sub

    Public Sub iniciarTransaccion()
        Me.conTran = New OleDb.OleDbConnection
        Me.cmdTran = New OleDb.OleDbCommand
        Me.conTran.ConnectionString = Me.string_conexion
        Me.conTran.Open()
        transaccion = Me.conTran.BeginTransaction()
        Me.cmdTran.Connection = conTran
        Me.cmdTran.Transaction = transaccion
        Me.cmdTran.CommandType = CommandType.Text
    End Sub
    Public Sub ejecutarTransaccion(ByVal consulta As String)
        Try
            Me.cmdTran.CommandText = consulta
            Me.cmdTran.ExecuteNonQuery()
            _resultado = estado_transaccion._ok
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            _resultado = estado_transaccion._error
        End Try
    End Sub
    '/////////////////////// porque necesito que me evuelva algo tmb ///////////
    Public Function devolverTransaccion(ByVal consulta As String) As DataTable
        Dim tabla As New DataTable
        Try
            Me.cmdTran.CommandText = consulta
            tabla.Load(Me.cmdTran.ExecuteReader())
            _resultado = estado_transaccion._ok
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            _resultado = estado_transaccion._error
        End Try
        Return tabla
    End Function
    Public Sub finalizarTransaccion()
        If _resultado = estado_transaccion._ok Then
            Me.transaccion.Commit()
        Else
            Me.transaccion.Rollback()
        End If
        Me.conTran.Close()
    End Sub
End Class
